﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using fideeraSoft.Utils;
using fideeraSoft.DAO;
using fideeraSoft.Entities;

namespace fideeraSoft.DAOimpl
{
    public class RemitoEntradaDAOSQLimpl:RemitoEntradaDAO
    {
        #region BUSCAR ULTIMO ID DE REMITO ENTRADA
        public int buscarNuevoUltimoIdRemitoEntradaEnBD()
        {
            int ID = 0;
            SqlConnection conn = SQLclass.conectarSQL();
            try
            {
                conn.Open();

                string query = "SELECT TOP 1 ID FROM RemitoEntrada ORDER BY ID DESC ";
                SqlCommand cmd = new SqlCommand(query, conn);

                SQLclass.commandQuery(conn, cmd, query);
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {

                    while (reader.Read())
                    {
                        ID = reader.GetInt32(0);
                        CLogger.Append("[buscarNuevoUltimoIdProveedorEnBD] ID : " + ID, CLogger.ERROR);      //DEBUG
                    }
                    ID++;
                }
                else
                {
                    ID = 1;
                }
            }
            catch (Exception e)
            {
                CLogger.Append("[buscarNuevoUltimoIdRemitoEntradaEnBD] Exception : " + e, CLogger.ERROR);      //DEBUG
                throw new MiException("[buscarNuevoUltimoIdRemitoEntradaEnBD] Exception: " + e);
            }
            finally
            {
                conn.Close();
            }
            // CLogger.Append("[buscarNuevoUltimoIdProveedorEnBD] RETURN ID : " + ID, CLogger.ERROR);      //DEBUG
            return ID;
        }

        #endregion

        #region ALTA Remito Entrada
        public void altaRemitoEntrada(RemitoEntrada remitoEntrada)
        {
            SqlConnection conn = null;
            try
            {
                conn = SQLclass.conectarSQL();
                conn.Open();

                string query = "SET IDENTITY_INSERT RemitoEntrada ON; INSERT INTO RemitoEntrada (ID,codigo,fechaEntrada,usuario,proveedor,fechaRecepcion) VALUES (@IdRemitoEntrada,@Codigo,@FechaEntrada,@IdUsuario,@IdProveedor,@FechaRecepcion)";
                //string query = "INSERT INTO Proveedor (razonSocial,responsable,cuil,direccion,localidad,provincia,telefono,celular) VALUES (@RazonSocial,@Responsable,@Cuil,@Direccion,@Localidad,@Provincia,@Telefono,@Celular)";
                SqlCommand cmd = new SqlCommand(query, conn);

                cmd.Parameters.AddWithValue("@IdRemitoEntrada", remitoEntrada.IdRemitoEntrada);
                cmd.Parameters.AddWithValue("@Codigo", remitoEntrada.Codigo);
                cmd.Parameters.AddWithValue("@FechaEntrada", remitoEntrada.DtFechaRemito);

                cmd.Parameters.AddWithValue("@IdUsuario", remitoEntrada.Usuario.IdUsuario);
                cmd.Parameters.AddWithValue("@IdProveedor", remitoEntrada.Proveedor.IdProveedor);
                
                cmd.Parameters.AddWithValue("@FechaRecepcion", remitoEntrada.DtFechaRecepcion);

                cmd.ExecuteNonQuery();
                //cmd.Parameters.Clear();
                // cmd.CommandText = "SELECT @@IDENTITY";
                // int insertID=Convert.ToInt32(cmd.ExecuteScalar());

                //  cmd.Dispose();
                cmd = null;


            }
            catch (Exception e)
            {
                CLogger.Append("[altaRemitoEntrada] Exception alta: " + e, CLogger.ERROR);      //DEBUG
                throw new MiException("[altaRemitoEntrada] Exception alta: " + e);
            }
            finally
            {
                conn.Close();
            }

        }

        #endregion

       

        #region BUSCAR REMITO ENTRADA POR COD

        public RemitoEntrada buscarRemitoEntradaPorCod(string cod)
        {
            RemitoEntrada remEntr= null;
            SqlConnection conn = SQLclass.conectarSQL();
            try
            {
                conn.Open();

                string query = "SELECT * FROM RemitoEntrada WHERE codigo='" + cod + "'";
                SqlCommand cmd = new SqlCommand(query, conn);

                SQLclass.commandQuery(conn, cmd, query);
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    remEntr = new RemitoEntrada();
                    while (reader.Read())
                    {
                        remEntr.IdRemitoEntrada = reader.GetInt32(0);
                        remEntr.Codigo=reader.GetString(1);
                        remEntr.DtFechaRemito= reader.GetDateTime(2);
                        remEntr.Usuario.IdUsuario= reader.GetInt32(3);
                        remEntr.Proveedor.IdProveedor= reader.GetInt32(4);
                        remEntr.DtFechaRecepcion= reader.GetDateTime(5);
                        
                        //  CLogger.Append("[buscarNuevoUltimoIdProveedorEnBD] ID : " + ID, CLogger.ERROR);      //DEBUG
                    }

                }
                else
                {
                    remEntr = null;
                }
            }
            catch (Exception e)
            {
                CLogger.Append("[buscarRemitoEntradaPorCod] Exception : " + e, CLogger.ERROR);      //DEBUG
                throw new MiException("[buscarRemitoEntradaPorCod] Exception: " + e);
            }
            finally
            {
                conn.Close();
            }
            // CLogger.Append("[buscarNuevoUltimoIdProveedorEnBD] RETURN ID : " + ID, CLogger.ERROR);      //DEBUG
            return remEntr;
        }       

        #endregion

        #region BUSCAR REMITO ENTRADA POR ID

        public RemitoEntrada buscarRemitoEntradaPorID(int id)
        {
            RemitoEntrada remEntr = null;
            SqlConnection conn = SQLclass.conectarSQL();
            try
            {
                conn.Open();

                string query = "SELECT * FROM RemitoEntrada WHERE ID='" + id + "'";
                SqlCommand cmd = new SqlCommand(query, conn);

                SQLclass.commandQuery(conn, cmd, query);
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    remEntr = new RemitoEntrada();
                    while (reader.Read())
                    {
                        remEntr.IdRemitoEntrada = reader.GetInt32(0);
                        remEntr.Codigo = reader.GetString(1);
                        remEntr.DtFechaRemito = reader.GetDateTime(2);
                        remEntr.Usuario.IdUsuario = reader.GetInt32(3);
                        remEntr.Proveedor.IdProveedor = reader.GetInt32(4);
                        remEntr.DtFechaRecepcion = reader.GetDateTime(5);

                        //  CLogger.Append("[buscarNuevoUltimoIdProveedorEnBD] ID : " + ID, CLogger.ERROR);      //DEBUG
                    }

                }
                else
                {
                    remEntr = null;
                }
            }
            catch (Exception e)
            {
                CLogger.Append("[buscarRemitoEntradaPorID] Exception : " + e, CLogger.ERROR);      //DEBUG
                throw new MiException("[buscarRemitoEntradaPorID] Exception: " + e);
            }
            finally
            {
                conn.Close();
            }
            // CLogger.Append("[buscarNuevoUltimoIdProveedorEnBD] RETURN ID : " + ID, CLogger.ERROR);      //DEBUG
            return remEntr;
        }

        #endregion

        #region ELIMINAR REMITO POR ID
        public void eliminarRemitoEntradaPorID(string id)
        {
            SqlConnection conn = SQLclass.conectarSQL();
            try
            {
                conn.Open();

                string query = "DELETE FROM RemitoEntrada WHERE ID='" + id + "'";
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                CLogger.Append("[eliminarRemitoEntradaPorID] Exception : " + e, CLogger.ERROR);      //DEBUG
                throw new MiException("[eliminarRemitoEntradaPorID] Exception: " + e);
            }
            finally
            {
                conn.Close();
            }
        }

        #endregion

        #region ELIMINAR TODOS LOS REMITOS ENTRADA EN DB
        public void eliminarTodosLosRemitosEntradaEnDb()
        {
            SqlConnection conn = SQLclass.conectarSQL();
            try
            {
                conn.Open();

                string query = "DELETE FROM RemitoEntrada";
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                CLogger.Append("[eliminarTodosLosRemitosEntradaEnDb] Exception : " + e, CLogger.ERROR);      //DEBUG
                throw new MiException("[eliminarTodosLosRemitosEntradaEnDb] Exception: " + e);
            }
            finally
            {
                conn.Close();
            }
        }

        #endregion
    }


}
